package com.kingdee.eas.custom.hostelmgr.beddistribution.app;

import java.util.Calendar;

import org.apache.log4j.Logger;

import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.eas.basedata.master.util.StringUtil;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.framework.report.util.RptParams;
import com.kingdee.eas.framework.report.util.RptRowSet;
import com.kingdee.eas.framework.report.util.SqlParams;
import com.kingdee.eas.util.app.DbUtil;

public class BedDistributionRptFacadeControllerBean extends
		AbstractBedDistributionRptFacadeControllerBean {
	private static Logger logger = Logger
			.getLogger("com.kingdee.eas.custom.hostelmgr.beddistribution.app.BedDistributionRptFacadeControllerBean");

	@Override
	protected void _createRptData(Context ctx) throws BOSException,
			EASBizException {

		super._createRptData(ctx);

		Calendar sDate = Calendar.getInstance();
		StringBuffer buffer = new StringBuffer();
		buffer
				.append("/*dialect*/INSERT INTO CT_HOS_BEDDISTRIBUTIONRPT (										\n");
		buffer.append("SELECT BUILDING.FID,															\n");

		buffer.append(sDate.get(Calendar.YEAR) + ",");
		buffer.append((sDate.get(Calendar.MONTH) + 1) + ",");
		buffer
				.append("COUNT(T.CFPERSONID) FROM CT_HOS_BEDDISTRIBUTION T	 						\n");
		buffer
				.append("LEFT JOIN CT_HOS_HOSTELBASIC BED ON T.CFTREEIDID=BED.FID						\n");
		buffer
				.append("LEFT JOIN CT_HOS_HOSTELBASIC ROOM ON BED.CFPARENTID=ROOM.FID					\n");
		buffer
				.append("LEFT JOIN CT_HOS_HOSTELBASIC FLOOR ON ROOM.CFPARENTID=FLOOR.FID				\n");
		buffer
				.append("LEFT JOIN CT_HOS_HOSTELBASIC UNIT ON FLOOR.CFPARENTID=UNIT.FID				\n");
		buffer
				.append("LEFT JOIN CT_HOS_HOSTELBASIC BUILDING ON UNIT.CFPARENTID=BUILDING.FID		\n");
		//buffer.append("WHERE T.CFSTAYDATE>=? AND T.CFSTAYDATE<?										\n");
		buffer
				.append("GROUP BY BUILDING.FID HAVING BUILDING.FID IS NOT NULL)														\n");

		DbUtil.execute(ctx, buffer.toString());
	}

	@Override
	public RptParams query(Context ctx, RptParams params) throws BOSException,
			EASBizException {
		SqlParams sqlParams = new SqlParams();

		StringBuffer buffer = new StringBuffer();
		buffer.append("/*dialect*/SELECT T.BUILDING.FNAME_L2,T.CFYEAR,				\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,1,T.CFPERSONQUANTITY,0)) AS ONE,		\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,2,T.CFPERSONQUANTITY,0)) AS TWO,		\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,3,T.CFPERSONQUANTITY,0)) AS THREE,		\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,4,T.CFPERSONQUANTITY,0)) AS FOUR,		\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,5,T.CFPERSONQUANTITY,0)) AS FIVE,		\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,6,T.CFPERSONQUANTITY,0)) AS SIX,		\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,7,T.CFPERSONQUANTITY,0)) AS SEVEN,		\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,8,T.CFPERSONQUANTITY,0)) AS EIGHT,		\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,9,T.CFPERSONQUANTITY,0)) AS NINE,		\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,10,T.CFPERSONQUANTITY,0)) AS TEN,		\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,11,T.CFPERSONQUANTITY,0)) AS ELEVEN,	\n");
		buffer
				.append("	MAX(DECODE(T.CFMONTH,12,T.CFPERSONQUANTITY,0)) AS TWELVE		\n");
		buffer.append("	FROM CT_HOS_BEDDISTRIBUTIONRPT T 							\n");
		buffer
				.append("	LEFT JOIN CT_HOS_HOSTELBASIC BUILDING ON T.CFBUILDINGID=BUILDING.FID	\n");
		if (!StringUtil.isEmpty(params.getString("year"))) {
			buffer.append("	WHERE T.CFYEAR=? 											\n");
			sqlParams.addString(params.getString("year"));
		}
		buffer
				.append("	GROUP BY CFYEAR,T.BUILDING.FNAME_L2	ORDER BY CFYEAR,T.BUILDING.FNAME_L2\n");
		RptRowSet rs = this.executeQuery(buffer.toString(), sqlParams, ctx);
		params.setObject("RESULT", rs);
		return params;
	}
}